package com.lecing.fetch.util;

import com.lecing.data.entity.Community;

import java.io.InputStream;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * 数据库工具类
 * Created by Administrator on 2018/5/11.
 */
public class DBUtil {
    static Properties prop=null;
    static String table ="";

    public static void main(String[] args){
        System.out.println("connection:"+getConn());
    }
    /**
     * 获取数据库连接
     * @return
     */
    private static Connection getConn() {
        InputStream inStream = DBUtil.class.getClassLoader().getResourceAsStream("dbconfig.properties");
          prop=new Properties();
        try {
            prop.load(inStream);
        }catch (Exception e){
            System.err.print("属性文件加载异常...");
            e.printStackTrace();
        }
        String driver = prop.getProperty("driver");
        String url = prop.getProperty("url");
        String username = prop.getProperty("username");
        String password = prop.getProperty("password");
        table = prop.getProperty("table");

        Connection conn = null;
        try {
            Class.forName(driver); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 插入小区数据
     * @param community
     * @return
     */
    public static int insert(Community community) {
        Connection conn = getConn();
        int i = 0;
        String sql = "INSERT INTO "+table+" (rity,blockno,blockn,road,longitude,latitude,test,crtime)\n" +
                "VALUES(?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, community.getCityNo()); //地市编码
           // pstmt.setString(2, community.getAreaCode());
            pstmt.setString(2, community.getComtyNo()); //小区ID
            pstmt.setString(3, community.getComtyName());
            pstmt.setString(4, community.getComtyAddr());
            pstmt.setDouble(5, community.getLongitude());
            pstmt.setDouble(6, community.getLatitude());
            pstmt.setInt(7, 1);
         //   pstmt.setLong(9,Long.parseLong(community.getComtyNo())); //小区十进制编码
            Date curDate= new Date(new java.util.Date().getTime());
            pstmt.setDate(8, curDate);
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 查询记录数
     * @return
     */
    public static Integer getAll(String cityCode) {
        Connection conn = getConn();
        String sql = "select * from sys_block where rity='"+cityCode+"'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            System.out.println("============================");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                }
                System.out.println("");
            }
            System.out.println("============================");
            return rs.getRow();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 删除
     * @param rity
     * @return
     */
    public static int delete(String rity) {
        Connection conn = getConn();
        int i = 0;
        String sql = "delete from sys_block where rity='" + rity + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i);
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
}
